﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Mono.TextTemplating;
using Newtonsoft.Json;
using WHLRDF.Application.BLL;
using WHLRDF.Application.Model;
using WHLRDF.Code.Model;
using WHLRDF.ORM;

namespace WHLRDF.Code.BLL
{
    /// <summary>
    /// Office 相关操作类
    /// </summary>
    public partial class DbCodeService
    {
        /// <summary>
        /// 表结构导出
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="targgetTableName"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] ExportTable(string tableid, ref string strError)
        {
            if (tableid.ToInt() <= 0)
            {
                strError = "参数不正确";
                return null;
            }
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return null;
            }
            ICriterion criter = Expression.And(
                Expression.Eq("DbTableId", tableid),
               Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return null;
            }
            return OfficeHelper.OutputExcel(sourceColumns);
        }


        /// <summary>
        /// 表结构word导出
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="targgetTableName"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] ExportWordTable(string tableid, ref string strError)
        {
            if (tableid.ToInt() <= 0)
            {
                strError = "参数不正确";
                return null;
            }
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return null;
            }
            ICriterion criter = Expression.And(
                Expression.Eq("DbTableId", tableid),
               Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return null;
            }
            return OfficeHelper.OutputWord<DbColumnEntity>((!string.IsNullOrWhiteSpace(sourceTable.LocalName) ? sourceTable.LocalName : sourceTable.DbTableName) + "数据字典", sourceColumns, null);
        }

        /// <summary>
        /// 下载导出模板
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="tableName">名称</param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] DownTableTemplate(string tableid, ref string tableName, ref string strError)
        {
            var table = this.GetTableById(tableid);
            var lstAll = GetColumns(tableid);
            if (table != null && lstAll != null && lstAll.Count > 0)
            {
                tableName = table.LocalName;
                var lstTempte = new List<ExcelTemplateEntity>();
                lstAll = lstAll.Where(x => (x.IsEditVisible && x.IsEnabled) || (x.IsPrimary && (!x.IsIdentifier || string.IsNullOrWhiteSpace(x.DefaultValue)))).OrderBy(x => x.OrderNo).ToList();
                lstAll.ForEach(x =>
                {
                    lstTempte.Add(new ExcelTemplateEntity
                    {
                        FieldName = x.ColumnName,
                        Name = x.ColumnCaption,
                        IsRequired = !x.AllowDBNull
                    });
                });
                if (lstTempte != null && lstTempte.Count > 0)
                {
                    return OfficeHelper.OutputExcelTemplate(lstTempte);
                }
            }
            strError = "未找到对应关系的结构";
            return null;

        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual byte[] Export(LigerGrid grid, string id)
        {
            var entity = this.GetById<DbTableEntity>(id);
            if (entity == null)
            {
                return null;
            }
            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId.ToString());
            if (dbServer == null)
            {
                return null;
            }
            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            var dbHelper = this.CreateRepository(dbServer.ProviderType, dbServer.ConntionString);
            dbHelper.GetSource(entity.DbTableName, grid);
            var dbColumn = this.GetColumns(entity.DbTableId.ToString());
            if (grid.Rows != null)
            {
                List<Dictionary<string, object>> dataRow = (List<Dictionary<string, object>>)grid.Rows;
                var visibleColumn = dbColumn.Where(x => x.IsEnabled && (x.IsEditVisible || x.IsGridVisible)).ToList();
                DataTable dt = new DataTable();
                List<string> title = new List<string>();
                List<string> columns = new List<string>();
                if (visibleColumn != null && visibleColumn.Count > 0)
                {
                    foreach (var item in visibleColumn)
                    {
                        title.Add(!string.IsNullOrWhiteSpace(item.ColumnCaption) ? item.ColumnCaption : item.ColumnName);
                        columns.Add(item.ColumnName);

                    }

                }
                return OfficeHelper.OutputExcel(dataRow, title, columns);

            }

            return null;

        }
        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="tableid">目标表</param>
        /// <param name="file">导入文件</param>
        /// <param name="strError">错误信息</param>
        /// <returns></returns>
        public bool Import(string tableid, IFormFile file, ref string strError)
        {
            if (tableid.ToInt() <= 0)
            {
                strError = "参数不正确";
                return false;
            }
            var table = this.GetTableById(tableid);
            if (table == null)
            {
                strError = "表未找到";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(table.DbServerId.ToString());
            if (targetServer == null ||string.IsNullOrWhiteSpace(targetServer.DbServerId))
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            var lstAll = OfficeHelper.ImportExcel<Dictionary<string, string>>(file);
            strError = JSONHelper.ToJson(lstAll);
            if (lstAll != null && lstAll.Count > 0)
            {
                ICriterion criter = Expression.And(
                Expression.Eq(DbTableEntity._PrimaryKeyName, tableid),
                Expression.Eq(DbTableEntity.__IsDeleted, false));
                var targetHelper = this.CreateRepository(targetServer.ProviderType, targetServer.ConntionString);
                var lstColumn = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
                if (lstColumn == null || lstColumn.Count <= 0)
                {
                    strError = "请先同步字段！";
                    return false;
                }
                var primaryKey = lstColumn.Where(x => x.IsPrimary).FirstOrDefault();
                if (primaryKey == null)
                {
                    strError = "该表未设置主键，请先设置主键";
                    return false;
                }
                List<TableSyncDomain> syncDomains = new List<TableSyncDomain>();
                int index = 0;

                var ifieldTypeService = AppHttpContext.GetSerivce<ISysFieldTypeService>();
                foreach (var rowItem in lstAll)
                {
                    bool isInsert = true;
                    Dictionary<string, object> resultParameter = new Dictionary<string, object>();
                    var primary = rowItem.Where(x => x.Key.ToLower().Equals(primaryKey.ColumnName.ToLower())
                        || (!string.IsNullOrWhiteSpace(primaryKey.ColumnCaption) && x.Key.ToLower().Equals(primaryKey.ColumnCaption.ToLower()))).FirstOrDefault();
                    string existSql = GetExistSql(targetHelper, table.DbTableName, primaryKey.ColumnName);

                    if (!primary.Equals(default(KeyValuePair<string, string>)) && !string.IsNullOrWhiteSpace(primary.Value))
                    {
                        DataParameterCollection primaryParameters = new DataParameterCollection(targetHelper);
                        primaryParameters.Add(primaryKey.ColumnName, primary.Value);
                        object obj = targetHelper.Scalar(existSql, primaryParameters);
                        if (obj != null && obj.ToString() == "1")
                        {
                            isInsert = false;
                        }
                    }
                    if (isInsert)
                    {
                        if (GetImportInsert(lstColumn, rowItem, index, ref resultParameter, ref strError))
                        {
                            TableSyncDomain syncDomain = new TableSyncDomain();
                            DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                            syncDomain.hql = GetInsert(targetHelper, table.DbTableName, resultParameter, ref dbParameters);
                            syncDomain.dbParameters = dbParameters;
                            syncDomains.Add(syncDomain);
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        if (GetImportUpdate(lstColumn, rowItem, index, ref resultParameter, ref strError))
                        {
                            TableSyncDomain syncDomain = new TableSyncDomain();
                            DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                            syncDomain.hql = GetUpdateSql(targetHelper, table.DbTableName, primaryKey.ColumnName, resultParameter, ref dbParameters);
                            syncDomain.dbParameters = dbParameters;
                            syncDomains.Add(syncDomain);
                        }
                        else
                        {
                            return false;
                        }
                    }
                    index++;
                }
                using (var tran = targetHelper.Begin())
                {
                    try
                    {
                        foreach (var syncDomain in syncDomains)
                        {
                            targetHelper.Execute(syncDomain.hql, syncDomain.dbParameters);
                        }
                        targetHelper.Commit();
                    }
                    catch (Exception ex)
                    {
                        targetHelper.Rollback();
                        strError = ex.Message;
                        return false;
                    }

                }
            }
            else
            {

                return false;
            }
            return true;
        }
    }
}
